<?php
/**
 * $Id: sql.php 23 2009-06-11 02:38:49Z eyou80 $
 */

/**
 * 使用方法:
 *
 * $sql = new Tinyask_Db_Sql();	//或者使用Tinyask_Db的sql方法返回实例化对象
 * $sql->select('post_id, post_title')->from('post')
 * ->where('post_id = %d', 1)
 * ->limit(1);
 * echo $sql;
 * 打印的结果将是
 * SELECT post_id, post_title FROM posts WHERE 1=1 AND post_id = 1 LIMIT 1
 */

/**
 * 数据库查询sql语句构造类
 *
 * @package Db
 */
class Tinyask_Db_Sql
{
	/** 数据库关键字 */
	const KEYWORDS = 'PRIMARY|AND|OR|LIKE|BINARY|BY|DISTINCT|AS|IN|IS|NULL';

	/**
	 * 数据库适配器
	 *
	 * @var Tinyask_Db_Adapter
	 */
	private $_adapter;

	/**
	 * 查询语句预结构,由数组构成,方便组合为SQL查询字符串
	 *
	 * @var array
	 */
	private $_sqlStruct;

	/**
	 * 表前缀
	 *
	 * @var string
	 */
	private $_prefix;

	/**
	 * 数据库关键字
	 *
	 * @var string
	 */
	private $_keywords;

	public function __construct(Tinyask_Db_Adapter $adapter, $prefix)
	{
		$this->_adapter = &$adapter;
		$this->_prefix = $prefix;
		$this->_keywords = self::KEYWORDS;

		$this->_sqlStruct = array(
			'action' => null,
			'table' => null,
			'fields' => '*',
			'join' => array(),
			'where' => null,
			'limit' => null,
			'offset' => null,
			'order' => null,
			'group' => null,
			'row' => array(),
		);
	}

	/**
	 * 过滤表前缀,表前缀由table.构成
	 *
	 * @param string $string 需要解析的字符串
	 * @return string
	 */
	private function filterPrefix($string)
	{
		return (0 === strpos($string, 'table.')) ? substr_replace($string, $this->_prefix, 0, 6) : $string;
	}

	/**
	 * 过滤数组键值
	 *
	 * @param string $string 待处理字段值
	 * @return string
	 */
	private function filterField($string)
	{
		return preg_replace_callback("/(['\"]?)(\s*)([_0-9a-zA-Z\.]+)(\(?)/", array($this, 'filterFieldCallback'), $string);
	}

	/**
	 * 数组正则匹配回调函数
	 *
	 * @param array $matches 匹配数组
	 * @return string
	 */
	private function filterFieldCallback(array $matches)
	{
		if(empty($matches[1]) && empty($matches[4]) && !is_numeric($matches[3][0]) &&
		!preg_match('/^(' . $this->_keywords . ')$/i', $matches[3]))
		{
			$pos = strpos($matches[3], '.');
			$pos = ($pos === false) ? 0 : $pos + 1;
			$field = $this->_adapter->quoteField(substr($matches[3], $pos));
			return $matches[2] . $this->filterPrefix(substr_replace($matches[3], $field, $pos));
		}
		else
		{
			return $matches[1] . $matches[2] . $matches[3] . $matches[4];
		}
	}

	/**
	 * 从参数中合成查询字段
	 *
	 * @param array $params
	 * @return string
	 */
	private function getFieldFromParams(array $params)
	{
		$fields = array();
		foreach($params as $val)
		{
			if(is_array($val))
			{
				foreach($val as $k => $v)
				{
					$fields[] = $k . ' AS ' . $v;
				}
			}
			else
			{
				$fields[] = $val;
			}
		}

		return $this->filterField(implode(', ', $fields));
	}

	/**
	 * 获取查询字串属性值
	 *
	 * @param string $name 属性名称
	 * @return string
	 */
	public function getAttribute($name)
	{
		return isset($this->_sqlStruct[$name]) ? $this->_sqlStruct[$name] : null;
	}

	/**
	 * 设置关键字
	 *
	 * @param string $keywords 关键字
	 * @return Tinyask_Db_Sql
	 */
	public function setKeywords($keywords = self::KEYWORDS)
	{
		$this->_keywords = $keywords;
		return $this;
	}

	/**
	 * 连接表
	 *
	 * @param string $table
	 * @param string $condition 连接条件
	 * @param string $op 链接方法
	 * @return Tinyask_Db_Sql
	 */
	public function join($table, $condition, $op = Tinyask_Db::LEFT_JOIN)
	{
		$this->_sqlStruct['join'][] = array($this->filterPrefix($table), $this->filterField($condition), $op);
		return $this;
	}

	/**
	 * AND条件查询语句
	 *
	 * @param string $condition 查询条件
	 * @param mixed $param 条件值
	 * @return Tinyask_Db_Sql
	 */
	public function where()
	{
		$condition = func_get_arg(0);
		$condition = str_replace('?', "%s", $this->filterField($condition));
		$op = empty($this->_sqlStruct['where']) ? ' WHERE' : ' AND';

		if(func_num_args() <= 1)
		{
			$this->_sqlStruct['where'] .= $op . ' (' . $condition . ')';
		}
		else
		{
			$args = func_get_args();
			array_shift($args);
			$this->_sqlStruct['where'] .= $op . ' ('. vsprintf($condition, array_map(array($this->_adapter, 'quoteValue'), $args)) . ')';
		}

		return $this;
	}

	/**
	 * OR条件查询语句
	 *
	 * @param string $condition 查询条件
	 * @param mixed $param 条件值
	 * @return Tinyask_Db_Sql
	 */
	public function orWhere()
	{
		$condition = func_get_arg(0);
		$condition = str_replace('?', "%s", $this->filteField($condition));
		$op = empty($this->_sqlStruct['where']) ? ' WHERE' : ' OR';

		if(func_num_args() <= 1)
		{
			$this->_sqlStruct['where'] .= $op . ' (' . $condition . ')';
		}
		else
		{
			$args = func_get_args();
			array_shift($args);
			$this->_sqlStruct['where'] .= $op . ' (' . vsprintf($condition, array_map(array($this->_adapter, 'quoteValue'), $args)) . ')';
		}

		return $this;
	}

	/**
	 * 查询行数限制
	 *
	 * @param integer $limit
	 * @return Tinyask_Db_Sql
	 */
	public function limit($limit)
	{
		$this->_sqlStruct['limit'] = intval($limit);
		return $this;
	}

	/**
	 * 查询行数偏移量
	 *
	 * @param integer $offset 需要偏移的行数
	 * @return Tinyask_Db_Sql
	 */
	public function offset($offset)
	{
		$this->_sqlStruct['offset'] = intval($offset);
		return $this;
	}

	/**
	 * 分页查询
	 *
	 * @param integer $page
	 * @param integer $pageSize
	 * @return Tinyask_Db_Sql
	 */
	public function page($page, $pageSize)
	{
		$pageSize = intval($pageSize);
		$this->_sqlStruct['limit'] = $pageSize;
		$this->_sqlStruct['offset'] = (max(intval($page), 1) - 1) * $pageSize;
		return $this;
	}

	/**
	 * 指定需要写入的列及其值
	 *
	 * @param array $row
	 * @return Tinyask_Db_Sql
	 */
	public function row(array $row)
	{
		foreach($row as $key => $value)
		{
			$this->_sqlStruct['row'][$this->filterField($key)] = empty($value) && $value !== 0 && $value !== '0' ? 'null' : $this->_adapter->quoteValue($value);
		}
		return $this;
	}

	/**
	 * 指定需要写入的列及其值, 单行
	 *
	 * @param string $key
	 * @param string $value
	 * @return Tinyask_Db_Sql
	 */
	public function express($key, $value)
	{
		$this->_sqlStruct['row'][$this->filterField($key)] = $this->quoteValue($value);
		return $this;
	}

	/**
	 * 排序顺序(ORDER BY)
	 *
	 * @param string $orderby
	 * @param string $sort
	 * @return Tinyask_Db_Sql
	 */
	public function order($orderby, $sort = Tinyask_Db::SORT_DESC)
	{
		$this->_sqlStruct['order'] = ' ORDER BY ' . $this->filterField($orderby) . (empty($sort) ? null : ' ' . $sort);
		return $this;
	}

	/**
	 * 集合聚集(GROUP BY)
	 *
	 * @param string $key 聚集的字段
	 * @return Tinyask_Db_Sql
	 */
	public function group($key)
	{
		$this->_sqlStruct['group'] = ' GROUP BY ' . $this->filterField($key);
		return $this;
	}

	/**
	 * 选择查询字段(SELECT)
	 *
	 * @param mixed $field
	 * @return Tinyask_Db_Sql
	 */
	public function select($field = '*')
	{
		$this->_sqlStruct['action'] = Tinyask_Db::SELECT;
		$args = func_get_args();

		$this->_sqlStruct['fields'] = $this->getFieldFromParams($args);
		return $this;
	}

	/**
	 * 设置查询操作表(SELECT)
	 *
	 * @param string $table
	 * @return Tinyask_Db_Sql
	 */
	public function from($table)
	{
		$this->_sqlStruct['table'] = $this->filterPrefix($table);
		return $this;
	}

	/**
	 * 更新语句(UPDATE)
	 *
	 * @param string $table
	 * @return Tinyask_Db_Sql
	 */
	public function update($table)
	{
		$this->_sqlStruct['action'] = Tinyask_Db::UPDATE;
		$this->_sqlStruct['table'] = $this->filterPrefix($table);
		return $this;
	}

	/**
	 * 删除语句(DELETE)
	 *
	 * @param string $table
	 * @return Tinyask_Db_Sql
	 */
	public function delete($table)
	{
		$this->_sqlStruct['action'] = Tinyask_Db::DELETE;
		$this->_sqlStruct['table'] = $this->filterPrefix($table);
		return $this;
	}

	/**
	 * 插入语句(INSERT)
	 *
	 * @param string $table
	 * @return Tinyask_Db_Sql
	 */
	public function insert($table)
	{
		$this->_sqlStruct['action'] = Tinyask_Db::INSERT;
		$this->_sqlStruct['table'] = $this->filterPrefix($table);
		return $this;
	}

	/**
	 * 返回sql语句
	 *
	 * @return string
	 */
	public function __toString()
	{
		switch($this->_sqlStruct['action'])
		{
			case Tinyask_Db::SELECT:
				return $this->_adapter->mergeSelect($this->_sqlStruct);
			case Tinyask_Db::INSERT:
				return 'INSERT INTO ' . $this->_sqlStruct['table'] . '(' . implode(', ', array_keys($this->_sqlStruct['row'])) . ')'
				. ' VALUES ' . '(' . implode(', ', array_values($this->_sqlStruct['row'])) . ')'
				. $this->_sqlStruct['limit'];
			case Tinyask_Db::DELETE:
				return 'DELETE FROM ' . $this->_sqlStruct['table'] . $this->_sqlStruct['where'];
			case Tinyask_Db::UPDATE:
				$fields = array();
				if(isset($this->_sqlStruct['row']))
				{
					foreach($this->_sqlStruct['row'] as $key => $val)
					{
						$fields[] = "$key = $val";
					}
				}
				return 'UPDATE ' . $this->_sqlStruct['table'] . ' SET ' . implode(', ', $fields) . $this->_sqlStruct['where'];
			default:
				return null;
		}
	}
}
